package com.personnelsystem;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class MySqlDatabaseUse implements DatabaseUse {
    boolean insert(Connection connection) throws SQLException {
        Statement statement = null;
        boolean x = false;
        Scanner scanner = new Scanner(System.in);
        try {
            System.out.println("--------请输入增加的员工信息---------");
            System.out.print("员工编号：");
            String id = scanner.nextLine();
            System.out.print("员工姓名：");
            String name = scanner.nextLine();
            System.out.print("员工薪水：");
            String money = scanner.nextLine();
            System.out.print("员工入职时间(只能使用格式：yyyy-MM-dd hh:mm:ss)：");
            String hiredate = scanner.nextLine();
            System.out.print("领导编号：");
            String managerId = scanner.nextLine();

            statement = connection.createStatement();
            //insert into employees(e_id,e_name,e_salary,e_hiredate,manager_id) values(200000,'李狗蛋',4666.0,'2020-12-12 10:00:00',100003);
            StringBuffer sql = new StringBuffer();
            sql.append("insert into employees(e_id,e_name,e_salary,e_hiredate,manager_id) values(");
            sql.append(id);
            sql.append(",'");
            sql.append(name);
            sql.append("',");
            sql.append(money);
            sql.append(",'");
            sql.append(hiredate);
            sql.append("',");
            sql.append(managerId);
            sql.append(")");
            x = statement.execute(sql.toString());
        }catch (Exception e){
            System.err.println("----增加出现异常----");
            return false;
        }finally {
            connection.close();
            statement.close();
        }
        System.out.println("------增加了一条数据------");
        return x;
    }
    boolean remove(Connection connection) throws SQLException {
        Statement statement = null;
        Scanner scanner = new Scanner(System.in);
        try {
            System.out.print("请输入要删除的员工的编号：");
            String id = scanner.nextLine();
            statement = connection.createStatement();
            //delete from employees where(e_id=111111)
            boolean x = statement.execute("delete from employees where(e_id="+id+")");
        }catch (Exception e){
            System.err.println("----删除出现异常----");
            return false;
        }finally {
            connection.close();
            statement.close();
        }
        System.out.println("------删除了一条数据------");
        return true;
    }
    //修改所有人数据
    boolean update(Connection connection) throws SQLException {
        Statement statement = null;
        Scanner scanner = new Scanner(System.in);
        try {
            System.out.print("请输入要修改的员工的编号：");
            String oldId = scanner.nextLine();
            System.out.println("--------请输入修改的员工信息---------");
            System.out.print("员工编号：");
            String id = scanner.nextLine();
            System.out.print("员工姓名：");
            String name = scanner.nextLine();
            System.out.print("员工薪水：");
            String money = scanner.nextLine();
            System.out.print("员工入职时间(只能使用格式：yyyy-MM-dd hh:mm:ss)：");
            String hiredate = scanner.nextLine();
            System.out.print("领导编号：");
            String managerId = scanner.nextLine();

            statement = connection.createStatement();
            //update employees set e_id=111111,e_name=张三,e_salary=3500,e_hiredate=2009-01-01 10:00:00,manager_id=100000 where(e_id=111111)
            boolean x = statement.execute("update employees set e_id="+id+",e_name='"+name+"',e_salary="+money+",e_hiredate='"+hiredate+"',manager_id="+managerId+" where(e_id="+oldId+")");
        }catch (Exception e){
            System.err.println("----修改出现异常----");
            return false;
        }finally {
            connection.close();
            statement.close();
        }
        System.out.println("------修改了一条数据------");
        return true;
    }

    //修改所有人工资
    boolean updateAll(Connection connection) throws SQLException {
        Statement statement = null;
        boolean x = false;
        try {
            Scanner scanner = new Scanner(System.in);
            System.out.println("----请输入全体升薪多少？（+表示加薪，-表示减薪）----");
            System.out.print("你的输入：");
            String money = scanner.nextLine();
            statement = connection.createStatement();
            x = statement.execute("update employees set e_salary=e_salary"+money);
        }catch (Exception e){
            System.err.println("----修改出现异常,记得输入+-号-----");
            return false;
        }finally {
            connection.close();
            statement.close();
        }
        System.out.println("-----修改了一条数据-----");
        return x;
    }

    //查询
    boolean select(Connection connection) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        try {
            statement = connection.createStatement();
            result = statement.executeQuery("select * from employees");
            while (result.next()){
                String id = result.getString(1);
                String name = result.getString(2);
                String salary = result.getString(3);
                String hiredate = result.getString(4);
                String managerId = result.getString(5);
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append("编号:");
                stringBuffer.append(id);
                stringBuffer.append(" 姓名:");
                stringBuffer.append(name);
                stringBuffer.append(" 薪水:");
                stringBuffer.append(salary);
                stringBuffer.append(" 入职时间:");
                stringBuffer.append(hiredate);
                stringBuffer.append(" 领导编号:");
                stringBuffer.append(managerId);
                System.out.println(stringBuffer.toString());
            }
        }catch (Exception e){
            System.err.println("-----查询出现异常-----");
            return false;
        }finally {
            connection.close();
            statement.close();
            result.close();
        }
        System.out.println("------查询结束----");
        return true;
    }
}
